* Tables 3, 4 and 9

use "temp/EIA_clean.dta", clear
xtset id year 
tsfill, full
gen i94 = 0
replace i94=1 if year==1994
bys year: egen siAGG = total(misi)
foreach x of var mi si xi {
gen `x'94b = 0
replace `x'94b = . if i94==1 & `x'==.
replace `x'94b = `x' * i94
bys id: egen `x'94 = total(`x'94b)
replace `x'94=. if `x'94b==. & `x'94==0
drop `x'94b
}
gen siAGG94b = siAGG * i94
bys id: egen siAGG94 = total(siAGG94b)
gen deltas = si - si94
gen deltam = mi - mi94
gen misi94  = mi94 * si94
gen NI = 0 // new importers
replace NI = 1 if (si>0 & si !=.) & (si94==0 | si94==.)
gen OI = 0 // firms that stop importing
replace OI = 1 if (si==0 | si ==.) & (si94>0 & si94 !=.)
gen CI=0 // continuing importers
replace CI =1 if si94>0 & si94!=. & si>0 & si!=.
gen withi = mi94 * deltas * CI
gen beti = si94 * deltam * CI
gen covi = deltas * deltam * CI

* Within
bys year: egen W  = total(withi) 
replace W = W / siAGG94 * 100
bys year: egen Wex1  = total(withi*exporter) 
replace Wex1 = Wex1 / siAGG94 * 100

* Between 
bys year: egen B  = total(beti) 
replace B = B / siAGG94 * 100
bys year: egen Bex1  = total(beti * exporter) // Exporter
replace Bex1 = Bex1 / siAGG94 * 100

* Covariance
bys year: egen C  = total(covi) 
replace C = C / siAGG94 * 100
bys year: egen Cex1  = total(covi* exporter) 
replace Cex1 = Cex1 / siAGG94 * 100 // Exporters

* Entry
bys year: egen E  = total(misi * NI) 
replace E = E / siAGG94 * 100
bys year: egen Eex1  = total(misi * NI * exporter) 
replace Eex1 = Eex1 / siAGG94 * 100 // Exporter

* Exit
bys year: egen X  = total(misi94 * OI) 
replace X = X / siAGG94 * 100
bys year: egen Xex1  = total(misi94 * OI *exporter) 
replace Xex1 = Xex1 / siAGG94 * 100 // Exporter

gen gsiAGG = (siAGG / siAGG94 - 1 ) * 100

collapse W Wex1 B Bex1 C Cex1 E Eex1 X Xex1 gsiAGG siAGG, by(year)
gen NE = E - X
gen NEEx1 = Eex1 - Xex1
drop E X Eex1 Xex1
label var W "Within"
label var B "Between"
label var C "Covariance"
label var NE "Net Entry"
label var gsiAGG "Total"
replace W = round(W,0.01)
replace B = round(B,0.01)
replace C = round(C,0.01)
replace NE = round(NE,0.01)
replace gsiAGG = round(gsiAGG,0.01)
drop if year<1995
keep if year<=1999
export excel year W B C NE gsiAGG using "../../Output/Table3_Mex", first(varl) sh("Table3") replace

* Table 4:
gen BC = B+C
gen BCex1 = Bex1 + Cex1
label var BC "Between + Covariance"
label var NEEx1 "Net Entry: Exporters"
gen growthE = Wex1 + BCex1 + NEEx1
replace growthE = round(growthE,0.01)
replace BCex1 = round(BCex1,0.01)
replace NEEx1 = round(NEEx1,0.01)
label var gsiAGG "Import Share Growth: Total"
label var growthE "Import Share Growth: Exporters"
export excel year gsiAGG growthE BC BCex1 NE NEEx1 using "../../Output/Table4_Mex", first(varl) sh("Table4") replace
* Table 9
keep if year==1999
drop year
label var Bex1 "Between: Exporters"
export excel Bex1 using "../../Output/Table9_Data", first(varl) sh("Table9") replace
